<?php
defined('_JEXEC') or die ('Acesso restringido');
jimport('joomla.application.component.modelform');

class BaseModelUsuarios extends JModel {

	public function datos_grid(){
	 	
 	$db = &JFactory::getDBO();
    $pagination = false;
	if (isset($_REQUEST["page"])) {
	   $pagination = true;	
	   $page = intval($_REQUEST["page"]);
	   
	}else{
		$page=0;
	}

	if(isset($_REQUEST["perpage"]))
		$perpage = intval($_REQUEST["perpage"]);
	else
		$perpage = 10;
		
		
	// this variables Omnigrid will send only if serverSort option is true
    if(isset($_REQUEST["sorton"])) { $sorton = $_REQUEST["sorton"]; }else{ $sorton = null; }
	if(isset($_REQUEST["sortby"])) { $sortby = $_REQUEST["sortby"]; }else{ $sortby = null; }
	
    if(isset($_REQUEST["id2"])) {   $id2 = $_REQUEST["id2"]; } else $id2=null;
    if(isset($_REQUEST["nombre"])) {        $nombre = $_REQUEST["nombre"]; } else $nombre=null;
    if(isset($_REQUEST["username"])) {      $username = $_REQUEST["username"]; } else $username=null;
    if(isset($_REQUEST["idtipo"])) {        $idtipo = $_REQUEST["idtipo"]; } else $idtipo=null;
    if(isset($_REQUEST["idcargo"])) {       $idcargo = $_REQUEST["idcargo"]; } else $idcargo=null;
        	
	$n = ( $page -1 ) * $perpage;
        $limit = "";
	
	if ( $pagination )
		$limit = "LIMIT $n, $perpage";
	
        $sql= "SELECT id2 as cedula,concat(name,' ',lastname) as nombre,username as usuario,telefono,direccion," .
        "tip.tipo as tipo,car.cargo as cargo," .
        "CONCAT('<a href=javascript:void(0); onclick=menu_usuario(',id,') >Menu</a>') as menu," .
        "CONCAT('<a href=javascript:void(0); onclick=editar_usuario(',id,') >Editar</a>') as editar  " .
        "from dtwoa_users us join dtwoa_tipos tip using(idtipo) join dtwoa_cargos car using(idcargo) ";
                        
        $sql.= " where id is not null ";
        if($id2) $sql.=" and id2 like ('%$id2%') ";
        if($nombre) $sql.=" and upper(concat(name,' ',lastname)) like upper('%$nombre%') ";
        if($username) $sql.=" and upper(username) like upper('%$username%') ";
        if($idtipo) $sql.=" and idtipo=$idtipo ";
        if($idcargo) $sql.=" and idcargo=$idcargo ";
			
	if($sorton) $sql.=" ORDER BY $sorton $sortby ";
	
	if($limit) $sql.=" $limit ";
	
	
        $db->setQuery($sql);
        $db->query();
        $total = $db->getNumRows();
        $row = $db->loadObjectList();
	
	$row = array("page"=>$page, "total"=>$total, "data"=>$row);

	echo json_encode($row);
	}

	function selectUsuarios($usuarios,$name='idusuario',$selected=null){
       $options[] = JHTML::_('select.option',0,'Seleccione','id','name');
       $options= array_merge( $options, $this->obtenerUsuarios($usuarios));
       return JHTML::_('select.genericlist',$options,$name, 'class="inputbox"','id','name', $selected);
    }
    
	public function obtenerUsuarios($usuarios=null){
 
		$db = &JFactory::getDBO();
		
		$sql="Select * , upper(concat(name,' ',lastname)) as usuario ";
		$sql.="From #__users ";
		$sql.="Where idestado=1 ";
		$sql.=" and upper(concat(name,' ',lastname)) like upper('%$usuarios%') ";
		if(is_numeric($usuarios)){$sql.=" OR id2 like '%$usuarios%' ";}
		if(is_numeric($usuarios)){$sql.=" OR id like '%$usuarios%' ";} 
		$sql.="Order by usuario ";
		
		$db->setQuery($sql);
		
     	return $db->loadObjectList();	
	}

/******************		Tipos		*************************************/

    function selectTipos($name='idtipo',$selected=null,$tabla=null){
       $options[] = JHTML::_('select.option',0,'Seleccione','idtipo','tipo');
       $options= array_merge( $options, $this->obtenerTipos($tabla));
       return JHTML::_('select.genericlist',$options,$name, 'class="inputbox"','idtipo','tipo', $selected);
    }

	function selectEstados($name='idestado',$idestado=null){
       $options[] = JHTML::_('select.option',1,'Activo','idestado','estado');
       $options[] = JHTML::_('select.option',2,'Inactivo','idestado','estado');
       return JHTML::_('select.genericlist',$options,$name, 'class="inputbox"','idestado','estado', $idestado);
    }
    
    function obtenerTipos($tabla=null,$idtipo=null){
     $db = &JFactory::getDBO();
     $sql="SELECT idtipo,tipo,tabla,idestado FROM #__tipos WHERE ";
     $sql.=" idtipo is not null  ";
     if($tabla) $sql.=" AND tabla='$tabla' ";
     if($idtipo) $sql.=" AND idtipo in ($idtipo) ";
       else $sql.=" and idestado=1 ";
     $sql.=" ORDER BY TIPO ";
     $db->setQuery($sql);
     return $db->loadObjectList();
    }
   
   
   function eliminarTipos($idtipo){
   	$db = &JFactory::getDBO();
     $sql="delete from  #__tipos where idtipo in ($idtipo) ";
     $db->setQuery($sql);
     if($db->query()){
     	return true;
     }else{
     	return false;
     } 
   }
   

   
   /**********************		Cargos		***************************************/
   
    function selectCargos($name='idcargo',$selected=null){
       $options[] = JHTML::_('select.option',0,'Seleccione','idcargo','cargo');
       $options= array_merge( $options, $this->obtenerCargos());
       return JHTML::_('select.genericlist',$options,$name, 'class="inputbox"','idcargo','cargo', $selected);
    }


    function obtenerCargos($idcargo=null){
     $db = &JFactory::getDBO();
     $sql="SELECT idcargo,cargo,idestado,CASE WHEN IDESTADO=1 THEN 'ACTIVO' ELSE 'INACTIVO' END AS estado FROM #__cargos WHERE idcargo is not null ";
     if($idcargo) $sql.=" AND idcargo in ($idcargo) ";
     $sql.=" ORDER BY IDCARGO ASC";
     $db->setQuery($sql);
     return $db->loadObjectList();
    }   
    
    
   function eliminarCargos($idcargo){
   	$db = &JFactory::getDBO();
     $sql="delete from  #__cargos where idcargo in ($idcargo) ";
     $db->setQuery($sql);
     if($db->query()){
     	return true;
     }else{
     	return false;
     } 
   }
   
    
    
  /***********************	Menus     ***********************************/
   function selectMenus($name='idmenu',$idmenu=null){
       $options[] = JHTML::_('select.option',0,'Seleccione','idmenu','menu');
       $options= array_merge( $options, $this->obtenerMenus());
       return JHTML::_('select.genericlist',$options,$name, 'class="inputbox"','idmenu','menu', $idmenu);
    }
    
    function obtenerMenus($idmenu=null){
    	$db = &JFactory::getDBO();
    	$sql="SELECT idmenu,menu,enlace,tip.idtipo as idtipo,tip.tipo as tipo" .
    			",CASE WHEN men.idestado=1 THEN 'ACTIVO' ELSE 'INACTIVO' END AS estado ,men.idestado as idestado FROM #__menus men " .
    			" join #__tipos tip using(idtipo) WHERE idmenu is not null ";
    	if($idmenu) $sql.=" AND idmenu in ($idmenu) ";
    	$db->setQuery($sql);
    	return $db->loadObjectList();
    }
    
   function eliminarMenus($idmenu){
   	$db = &JFactory::getDBO();
     $sql="delete from  #__menus where idmenu in ($idmenu) ";
     $db->setQuery($sql);
     if($db->query()){
     	return true;
     }else{
     	return false;
        } 
     }  

}
?>